package com.zktr.dao;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.zktr.pojo.AdminRole;
/**

 * 从指定的Excel中读取数据到List集合中

 * @author yf

 */

public class ReadExcelToDB {

       /**

        * 将Excel中的数据按顺序读取到一个集合中

        * @param fl

        * @return

        */
		 public static void main(String[] args) throws Exception  {
			 ReadExcelToDB readExcelToDB = new ReadExcelToDB();
			 
			List<AdminRole> insertDB = readExcelToDB.insertDB();
			System.out.println( insertDB);
			readExcelToDB.insertDB();
			AdminRoleDao adminRoleDao = new AdminRoleDao();
			for (AdminRole adminRole : insertDB) {
				adminRoleDao.insertOneAdmin(adminRole);
			}
		    }

	public List<AdminRole> insertDB() throws IOException, FileNotFoundException {
		File excelFile = new File("e:\\admin.xls");
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(excelFile));
		AdminRole ar = null;
		List<AdminRole> list = new ArrayList<AdminRole>();
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			
			if (hssfSheet == null) {
				continue;
			}
			System.out.println(hssfSheet.getLastRowNum());

		System.out.println(hssfSheet.getLastRowNum());
		for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow == null) {
				continue;
			}
			ar = new AdminRole();
			
			HSSFCell yxsmc = hssfRow.getCell(0);
			if (yxsmc == null) {
				continue;
			}
			
			ar.setAdminrole((int)Double.parseDouble(getValue(yxsmc)));
			HSSFCell xm = hssfRow.getCell(1);
		
			if (xm == null) {
				continue;
			}
			ar.setUserid((int)Double.parseDouble(getValue(yxsmc)));
			HSSFCell xh = hssfRow.getCell(2);
			if (xh == null) {
				continue;
			}
			ar.setRoleid((int)Double.parseDouble(getValue(yxsmc)));
			list.add(ar);
		}
		}
		System.out.println(hssfWorkbook.getSheetName(0));
		return list;
	}
	@SuppressWarnings("static-access")
	private  String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			// 返回数值类型的值
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			// 返回字符串类型的值
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

}
